﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.Odbc;
using System.Text;
using Besture.Common;
using Besture.Model;

namespace Besture.Dal
{
    public partial class UserBaseDal : BaseDataAccess
    {

        /// <summary>
        /// 根据登录名或手机或邮箱获取数据
        /// </summary>
        /// <param name="userName">登录名或手机或邮箱</param>
        /// <returns></returns>
        public UserBaseModel GetDataByUser(string userName)
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append("SELECT * FROM TBUserBase WHERE (Phone=@UserName OR Email=@UserName) AND IsDeleted=@IsDeleted  ORDER BY ID DESC ");
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                base.SetParameter(ldcDBCommand, "@UserName", DbType.String, userName);
                base.SetParameter(ldcDBCommand, "@IsDeleted", DbType.Int32, Constants.ISDELETED_NO);
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                if (dt.Rows.Count > 0)
                    return ModelConvertHelper<UserBaseModel>.ConvertToModelList(dt)[0];
                else return null;
            }
            catch (Exception exception)
            {
                WriteLog(exception.ToString());
                return null;
            }
        }

        public int GetDataTableByPhoneOrEmail(string sPhoneEmail, int iUserType)
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                string sWhere = string.Empty;
                if (iUserType>0)
                {
                    sWhere =string.Format(" and UserType={0}",iUserType);
                }
                selectSql.Append(string.Format("select * from TBUserBase where (Phone='{0}' or Email='{0}') and IsDeleted=0 {1}", sPhoneEmail, sWhere));
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                if (dt.Rows.Count > 0) return 1;
                else
                    return 0;
            }
            catch (Exception exception)
            {
                return 0;
            }
        }

        public DataTable GetDataTableByUserType(int iUserType)
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append(string.Format("SELECT (select LoginName from TBAdminUser where ID =tbub.ReviewerID ) ReviewerName," +
                                                " case tbub.CheckStatus when 0 then '审核中' when 1 then '已审核' when 2 then '取消审核' end CheckStatusName,"+
                                               " tbub.* FROM TBUserBase tbub where tbub.UserType={0} ORDER BY tbub.UpdateTime DESC ", iUserType));
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                return dt;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 获取详细信息
        /// </summary>
        /// <param name="id">主键值</param>
        /// <returns></returns>
        public UserBaseModel GetModel(int id)
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append("SELECT * FROM TBUserBase");
                selectSql.Append(" WHERE ");
                selectSql.Append(" ID=@ID ");
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.Int32, id);
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                if (dt.Rows.Count > 0)
                    return ModelConvertHelper<UserBaseModel>.ConvertToModelList(dt)[0];
                else return null;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 获取全部数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataTable()
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append("SELECT * FROM TBUserBase ORDER BY UpdateTime DESC ");
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                return dt;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(UserBaseModel model)
        {
            var identityId = -1;
            try
            {

				
            string sqlString = string.Format(@"
                INSERT INTO TBUserBase
                ( 
                        LoginName
                        ,NickName
                        ,LoginPwd
                        ,UserType
                        ,Phone
                        ,Email
                        ,RealName
                        ,Sex
                        ,UserImage
                        ,Birthday
                        ,AreaCode
                        ,Tel
                        ,TelExt
                        ,ProvinceID
                        ,CityID
                        ,CompanyName
                        ,Representative
                        ,Address
                        ,UserRating
                        ,RatingIntegration
                        ,UserPoints
                        ,ReviewerID
                        ,CheckStatus
                        ,StatusTime
                        ,SortID
                        ,IsDeleted
                        ,Created
                        ,CreateTime
                        ,Updated
                        ,UpdateTime
                )
                VALUES
                (
                       @LoginName
                       ,@NickName
                       ,@LoginPwd
                       ,@UserType
                       ,@Phone
                       ,@Email
                       ,@RealName
                       ,@Sex
                       ,@UserImage
                       ,@Birthday
                       ,@AreaCode
                       ,@Tel
                       ,@TelExt
                       ,@ProvinceID
                       ,@CityID
                       ,@CompanyName
                       ,@Representative
                       ,@Address
                       ,@UserRating
                       ,@RatingIntegration
                       ,@UserPoints
                       ,@ReviewerID
                       ,@CheckStatus
                       ,@StatusTime
                       ,@SortID
                       ,@IsDeleted
                       ,@Created
                       ,@CreateTime
                       ,@Updated
                       ,@UpdateTime
                )SELECT @@IDENTITY");

            DbCommand ldcDBCommand = base.GetCommand(sqlString);

            //参数设定
            SetParameter(ldcDBCommand, "@LoginName", DbType.String, model.LoginName);
            SetParameter(ldcDBCommand, "@NickName", DbType.String, model.NickName);
            SetParameter(ldcDBCommand, "@LoginPwd", DbType.String, model.LoginPwd);
            SetParameter(ldcDBCommand, "@UserType", DbType.Int32, model.UserType);
            SetParameter(ldcDBCommand, "@Phone", DbType.String, model.Phone);
            SetParameter(ldcDBCommand, "@Email", DbType.String, model.Email);
            SetParameter(ldcDBCommand, "@RealName", DbType.String, model.RealName);
            SetParameter(ldcDBCommand, "@Sex", DbType.Int32, model.Sex);
            SetParameter(ldcDBCommand, "@UserImage", DbType.String, model.UserImage);
            SetParameter(ldcDBCommand, "@Birthday", DbType.DateTime, model.Birthday);
            SetParameter(ldcDBCommand, "@AreaCode", DbType.String, model.AreaCode);
            SetParameter(ldcDBCommand, "@Tel", DbType.String, model.Tel);
            SetParameter(ldcDBCommand, "@TelExt", DbType.String, model.TelExt);
            SetParameter(ldcDBCommand, "@ProvinceID", DbType.Int32, model.ProvinceID);
            SetParameter(ldcDBCommand, "@CityID", DbType.Int32, model.CityID);
            SetParameter(ldcDBCommand, "@CompanyName", DbType.String, model.CompanyName);
            SetParameter(ldcDBCommand, "@Representative", DbType.String, model.Representative);
            SetParameter(ldcDBCommand, "@Address", DbType.String, model.Address);
            SetParameter(ldcDBCommand, "@UserRating", DbType.Int32, model.UserRating);
            SetParameter(ldcDBCommand, "@RatingIntegration", DbType.Int32, model.RatingIntegration);
            SetParameter(ldcDBCommand, "@UserPoints", DbType.Int32, model.UserPoints);
            SetParameter(ldcDBCommand, "@ReviewerID", DbType.Int32, model.ReviewerID);
            SetParameter(ldcDBCommand, "@CheckStatus", DbType.Int32, model.CheckStatus);
            SetParameter(ldcDBCommand, "@StatusTime", DbType.DateTime, model.StatusTime);
            SetParameter(ldcDBCommand, "@SortID", DbType.Int32, model.SortID);
            SetParameter(ldcDBCommand, "@IsDeleted", DbType.Int32, model.IsDeleted);
            SetParameter(ldcDBCommand, "@Created", DbType.Int32, model.Created);
            SetParameter(ldcDBCommand, "@CreateTime", DbType.DateTime, model.CreateTime);
            SetParameter(ldcDBCommand, "@Updated", DbType.Int32, model.Updated);
            SetParameter(ldcDBCommand, "@UpdateTime", DbType.DateTime, model.UpdateTime);



                identityId = base.ExecuteScalarInsert(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return identityId;
        }
 

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(UserBaseModel model)
        {
            int iTmp = -1;
            try
            {

                string sqlString = string.Format(@"
                UPDATE TBUserBase
                SET
                    LoginName = @LoginName
                    ,NickName = @NickName
                    ,LoginPwd = @LoginPwd
                    ,UserType = @UserType
                    ,Phone = @Phone
                    ,Email = @Email
                    ,RealName = @RealName
                    ,Sex = @Sex
                    ,UserImage = @UserImage
                    ,Birthday = @Birthday
                    ,AreaCode = @AreaCode
                    ,Tel = @Tel
                    ,TelExt = @TelExt
                    ,ProvinceID = @ProvinceID
                    ,CityID = @CityID
                    ,CompanyName = @CompanyName
                    ,Representative = @Representative
                    ,Address = @Address
                    ,UserRating = @UserRating
                    ,RatingIntegration = @RatingIntegration
                    ,UserPoints = @UserPoints
                    ,ReviewerID = @ReviewerID
                    ,CheckStatus = @CheckStatus
                    ,StatusTime = @StatusTime
                    ,SortID = @SortID
                    ,IsDeleted = @IsDeleted
                    ,Created = @Created
                    ,Updated = @Updated
                    ,UpdateTime = @UpdateTime
                WHERE
                    ID = @ID
            ");

                DbCommand ldcDBCommand = base.GetCommand(sqlString);
                //绑定SQL参数
                SetParameter(ldcDBCommand, "@ID", DbType.Int32, model.ID);
                SetParameter(ldcDBCommand, "@LoginName", DbType.String, model.LoginName);
                SetParameter(ldcDBCommand, "@NickName", DbType.String, model.NickName);
                SetParameter(ldcDBCommand, "@LoginPwd", DbType.String, model.LoginPwd);
                SetParameter(ldcDBCommand, "@UserType", DbType.Int32, model.UserType);
                SetParameter(ldcDBCommand, "@Phone", DbType.String, model.Phone);
                SetParameter(ldcDBCommand, "@Email", DbType.String, model.Email);
                SetParameter(ldcDBCommand, "@RealName", DbType.String, model.RealName);
                SetParameter(ldcDBCommand, "@Sex", DbType.Int32, model.Sex);
                SetParameter(ldcDBCommand, "@UserImage", DbType.String, model.UserImage);
                SetParameter(ldcDBCommand, "@Birthday", DbType.DateTime, model.Birthday);
                SetParameter(ldcDBCommand, "@AreaCode", DbType.String, model.AreaCode);
                SetParameter(ldcDBCommand, "@Tel", DbType.String, model.Tel);
                SetParameter(ldcDBCommand, "@TelExt", DbType.String, model.TelExt);
                SetParameter(ldcDBCommand, "@ProvinceID", DbType.Int32, model.ProvinceID);
                SetParameter(ldcDBCommand, "@CityID", DbType.Int32, model.CityID);
                SetParameter(ldcDBCommand, "@CompanyName", DbType.String, model.CompanyName);
                SetParameter(ldcDBCommand, "@Representative", DbType.String, model.Representative);
                SetParameter(ldcDBCommand, "@Address", DbType.String, model.Address);
                SetParameter(ldcDBCommand, "@UserRating", DbType.Int32, model.UserRating);
                SetParameter(ldcDBCommand, "@RatingIntegration", DbType.Int32, model.RatingIntegration);
                SetParameter(ldcDBCommand, "@UserPoints", DbType.Int32, model.UserPoints);
                SetParameter(ldcDBCommand, "@ReviewerID", DbType.Int32, model.ReviewerID);
                SetParameter(ldcDBCommand, "@CheckStatus", DbType.Int32, model.CheckStatus);
                SetParameter(ldcDBCommand, "@StatusTime", DbType.DateTime, model.StatusTime);
                SetParameter(ldcDBCommand, "@SortID", DbType.Int32, model.SortID);
                SetParameter(ldcDBCommand, "@IsDeleted", DbType.Int32, model.IsDeleted);
                SetParameter(ldcDBCommand, "@Created", DbType.Int32, model.Created);
                SetParameter(ldcDBCommand, "@Updated", DbType.Int32, model.Updated);
                SetParameter(ldcDBCommand, "@UpdateTime", DbType.DateTime, model.UpdateTime);


                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                WriteLog(exception.ToString());
            }
            return iTmp;
        }


        /// <summary>
        /// 单个删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delete(int id)
        {
            int iTmp = -1;
            try
            {
                StringBuilder updateSql = new StringBuilder();
                updateSql.Append("DELETE FROM TBUserBase");
                updateSql.Append(" WHERE ");
                updateSql.Append(" ID=@ID ");
                DbCommand ldcDBCommand = base.GetCommand(updateSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.Int32, id);
                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="idList"></param>
        /// <returns></returns>
        public int DeleteList(string ids)
        {
            int iTmp = -1;
            try
            {
                StringBuilder updateSql = new StringBuilder();
                updateSql.Append("exec('DELETE FROM TBUserBase");
                updateSql.Append(" WHERE ");
                updateSql.Append(" ID in ('+@ID+') ')");
                DbCommand ldcDBCommand = base.GetCommand(updateSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.String, ids);
                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }


        /// <summary>
        /// 批量 审核
        /// </summary>
        /// <param name="idList"></param>
        /// <returns></returns>
        public int DeleteList(int iReviewerID ,int iCheckStatus, string sIds)
        {
            int iTmp = -1;
            try
            {

                string updateSql = string.Format("update TBUserBase set ReviewerID={0},CheckStatus={1},StatusTime=GETDATE() where ID in ({2})",
                   iReviewerID, iCheckStatus, sIds);
                DbCommand ldcDBCommand = base.GetCommand(updateSql);
                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Register(UserBaseModel model)
        {
            var identityId = -1;
            try
            {
                StringBuilder insertSql = new StringBuilder();
                insertSql.Append("INSERT INTO TBUserBase(");
                insertSql.Append("LoginName,");
                insertSql.Append("NickName,");
                insertSql.Append("LoginPwd,");
                insertSql.Append("Phone,");
                insertSql.Append("Email,");
                insertSql.Append("UserType,");
                insertSql.Append("CheckStatus,");
                insertSql.Append("UserPoints,");
                insertSql.Append("ReviewerID,");
                insertSql.Append("StatusTime,");
                insertSql.Append("Created,");
                insertSql.Append("CreateTime,");
                insertSql.Append("IsDeleted,");
                insertSql.Append("Updated,");
                insertSql.Append("UpdateTime,");
                insertSql.Append("SortID");
                

                insertSql.Append(") ");
                insertSql.Append(" VALUES(");
                insertSql.Append("@LoginName,");
                insertSql.Append("@NickName,");
                insertSql.Append("@LoginPwd,");
                insertSql.Append("@Phone,");
                insertSql.Append("@Email,");
                insertSql.Append("@UserType,");
                insertSql.Append("@CheckStatus,");
                insertSql.Append("@UserPoints,");
                insertSql.Append("@ReviewerID,");
                insertSql.Append("@StatusTime,");
                insertSql.Append("@Created,");
                insertSql.Append("@CreateTime,");
                insertSql.Append("@IsDeleted,");
                insertSql.Append("@Updated,");
                insertSql.Append("@UpdateTime,");
                insertSql.Append("@SortID");

                insertSql.Append(")");
                insertSql.Append(" SELECT @@IDENTITY");
                DbCommand ldcDBCommand = base.GetCommand(insertSql.ToString());
                base.SetParameter(ldcDBCommand, "@LoginName", DbType.String, model.LoginName);
                base.SetParameter(ldcDBCommand, "@NickName", DbType.String, model.NickName);
                base.SetParameter(ldcDBCommand, "@LoginPwd", DbType.String, model.LoginPwd);
                base.SetParameter(ldcDBCommand, "@Phone", DbType.String, model.Phone);
                base.SetParameter(ldcDBCommand, "@Email", DbType.String, model.Email);
                base.SetParameter(ldcDBCommand, "@UserType", DbType.String, model.UserType);
                base.SetParameter(ldcDBCommand, "@CheckStatus", DbType.Int32, model.CheckStatus);
                base.SetParameter(ldcDBCommand, "@UserPoints", DbType.Int32, 0);
                base.SetParameter(ldcDBCommand, "@ReviewerID", DbType.Int32, 0);
                base.SetParameter(ldcDBCommand, "@StatusTime", DbType.DateTime, model.CreateTime);
                base.SetParameter(ldcDBCommand, "@Created", DbType.Int32, model.CreateBy);
                base.SetParameter(ldcDBCommand, "@CreateTime", DbType.DateTime, model.CreateTime);
                base.SetParameter(ldcDBCommand, "@IsDeleted", DbType.Int32, model.IsDeleted);
                base.SetParameter(ldcDBCommand, "@Updated", DbType.Int32, model.Updated);
                base.SetParameter(ldcDBCommand, "@UpdateTime", DbType.DateTime, model.UpdateTime);
                base.SetParameter(ldcDBCommand, "@SortID", DbType.Int32, model.SortID);

                identityId = base.ExecuteScalarInsert(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return identityId;
        }

        /// <summary>
        /// Login
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public UserBaseModel Login(string UserName, string password)
        {
            try
            {
                DbCommand ldcDBCommand = null;
                StringBuilder selectSql = null;
                DataTable dt = null;
                selectSql = new StringBuilder();
                dt = new DataTable();

                selectSql.Append("SELECT * FROM TBUserBase");
                    selectSql.Append(" WHERE (Phone=@LoginName or Email=@LoginName) and LoginPwd=@LoginPwd");
                    ldcDBCommand = base.GetCommand(selectSql.ToString());
                    base.SetParameter(ldcDBCommand, "@LoginName", DbType.String, UserName);
                    base.SetParameter(ldcDBCommand, "@LoginPwd", DbType.String, password);
                    dt = base.GetDataSet(ldcDBCommand).Tables[0];

                    if (dt.Rows.Count == 0)
                    {
                        selectSql = new StringBuilder();
                        dt = new DataTable();
                        ldcDBCommand = new OdbcCommand();
                        selectSql.Append("SELECT * FROM TBUserBase");
                        selectSql.Append(" WHERE ");
                        selectSql.Append(" Phone=@LoginName and LoginPwd=@LoginPwd");
                        ldcDBCommand = base.GetCommand(selectSql.ToString());
                        base.SetParameter(ldcDBCommand, "@LoginName", DbType.String, UserName);
                        base.SetParameter(ldcDBCommand, "@LoginPwd", DbType.String, password);
                        dt = base.GetDataSet(ldcDBCommand).Tables[0];
                    }
               
                if (dt.Rows.Count > 0)
                    return ModelConvertHelper<UserBaseModel>.ConvertToModelList(dt)[0];
                else return null;
            }
            catch (Exception)
            {
                throw;
            }
        }

    }
}

